package BL;

import java.sql.*;

public class AccessDB {

	public AccessDB() {
		super();
	}

	public static String[] getKat() throws SQLException, ClassNotFoundException {
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager
				.getConnection(
						"jdbc:hsqldb:file:/Users/julianruess/Documents/201301_Java_Projekt/HSQLDB_AroundMe;shutdown=true",
						"sa", "");

		// SQL-Anweisungs-Objekt erzeugen
		Statement st = con.createStatement();

		ResultSet rs = st.executeQuery("Select  count(Name) from Kategorien");
		rs.next();
		int laenge = Integer.parseInt(rs.getString(1));

		rs.close();

		rs = st.executeQuery("Select Name from Kategorien ");

		String[] kat = new String[laenge];

		int i = 0;
		while (rs.next()) {
			kat[i] = new String(rs.getString(1));
			i++;
		}

		return kat;
	}

	public static Eintrag[] getErgebnisliste(String kat) throws SQLException,
			ClassNotFoundException {
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager
				.getConnection(
						"jdbc:hsqldb:file:/Users/julianruess/Documents/201301_Java_Projekt/HSQLDB_AroundMe;shutdown=true",
						"sa", "");
		// SQL-Anweisungs-Objekt erzeugen
		Statement st = con.createStatement();

		kat = kat.toUpperCase();
		ResultSet rs = st.executeQuery("Select  count(e.ID) from Eintraege e, Kategorien k, Verknuepfung v Where e.ID = v.EintragID AND v.KategorieID = k.ID AND UPPER(k.Name) = '"+kat+"'");
		rs.next();
		int laenge = Integer.parseInt(rs.getString(1));
		rs.close();
		
		rs = st.executeQuery("Select  e.ID, e.Name, e.LAT, e.LANG from Eintraege e, Kategorien k, Verknuepfung v Where e.ID = v.EintragID AND v.KategorieID = k.ID AND UPPER(k.Name) = '"+kat+"'");

		Eintrag[] ergList = new Eintrag[laenge];

		int i = 0;
		while (rs.next()) {
			Eintrag temp = new Eintrag();
			temp.setID(Integer.parseInt(rs.getString(1)));
			temp.setName(rs.getString(2));
			temp.setLat(Double.parseDouble(rs.getString(3)));
			temp.setLang(Double.parseDouble(rs.getString(4)));
			ergList[i] = temp;
			i++;
		}


		rs.close();
		st.close();
		con.close();

		return ergList;
	}

	public static User[] getUser() throws SQLException, ClassNotFoundException {
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager
				.getConnection(
						"jdbc:hsqldb:file:/Users/julianruess/Documents/201301_Java_Projekt/HSQLDB_AroundMe;shutdown=true",
						"sa", "");

		// SQL-Anweisungs-Objekt erzeugen
		Statement st = con.createStatement();

		ResultSet rs = st.executeQuery("Select ID From User");
		rs.next();
		int laenge = Integer.parseInt(rs.getString(1));
		rs.close();

		rs = st.executeQuery("Select * From User");

		User[] usr = new User[laenge];

		int i = 0;
		while (rs.next()) {
			User temp = new User();
			temp.setID(Integer.parseInt(rs.getString(0)));
			temp.setUser(rs.getString(1));
			temp.setPassword(rs.getString(2));

			i++;

		}

		rs.close();
		st.close();
		con.close();

		return usr;
	}

	public static Eintrag[] getErgebnisliste() throws SQLException,
			ClassNotFoundException {
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con = DriverManager
				.getConnection(
						"jdbc:hsqldb:file:/Users/julianruess/Documents/201301_Java_Projekt/HSQLDB_AroundMe;shutdown=true",
						"sa", "");

		// SQL-Anweisungs-Objekt erzeugen
		Statement st = con.createStatement();

		ResultSet rs = st.executeQuery("Select  count(ID) from Eintraege");
		rs.next();
		int laenge = Integer.parseInt(rs.getString(1));
		rs.close();

		rs = st.executeQuery("Select  ID, Name, LAT, LANG from Eintraege");

		Eintrag[] ergList = new Eintrag[laenge];

		int i = 0;
		while (rs.next()) {
			Eintrag temp = new Eintrag();
			temp.setID(Integer.parseInt(rs.getString(1)));
			temp.setName(rs.getString(2));
			temp.setLat(Double.parseDouble(rs.getString(3)));
			temp.setLang(Double.parseDouble(rs.getString(4)));
			ergList[i] = temp;
			i++;

		}

		rs.close();
		st.close();
		con.close();

		return ergList;
	}


	public static String getSelectAllString() throws SQLException,
			ClassNotFoundException {
		// spezifische Datenbanktreiber laden
		Class.forName("org.hsqldb.jdbcDriver");

		// Verbindung zur Datenbank "test" aufbauen
		Connection con = DriverManager
				.getConnection(
						"jdbc:hsqldb:file:/Users/julianruess/Documents/201301_Java_Projekt/HSQLDB_AroundMe;shutdown=true",
						"sa", "");

		// SQL-Anweisungs-Objekt erzeugen
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM Standorte");
		String all = new String("");

		// Resultat der SQL-Anweisung verarbeiten
		/*
		 * for (int i=1; rs.next() ; i++){ all = all + rs.getString(i); }
		 */

		while (rs.next()) {
			all = "\t" + all + rs.getString(1) + " | ";
			all = all + rs.getString(2) + " | ";
			all = all + rs.getString(3) + " | ";
			all = all + rs.getString(4) + " ";

		}

		rs.close();
		st.close();
		con.close();

		return all;
	}

	
}
